<?php

// database.php by Matt Fritz
// April 13, 2010
// Contains the database connection

$databaseServer = "mysql"; // name of the MySQL host server
$databaseName = "INF132"; // name of the MySQL database

$databaseUsername = "inf132"; // username of the database user
$databasePassword = "paradox"; // password of the database user
$encryptionKey = "paradox"; // key to use for the AES encryption algorithm

$databaseConnection = mysql_connect($databaseServer, $databaseUsername, $databasePassword); // database connection object

$databaseSelected = mysql_select_db($databaseName, $databaseConnection); // select the desired database

// generate an AES encryption method call for a given value
function getEncryptionFunction($value)
{
	global $encryptionKey;
	return "AES_ENCRYPT('$value', '$encryptionKey')";
}

// generate a MySQL login SELECT statement given a username and a password
function getEncryptedLoginSQL($username, $password)
{
	return "SELECT username,type,firstName,lastName FROM Users WHERE username = '$username' AND password = " . getEncryptionFunction($password) . ";";
}

// sanitize the given input
function sanitizeInput($string)
{
	return mysql_real_escape_string($string);
}

// figure out whether a given username already exists
function usernameExists($username)
{
	// build the SELECT query
	$uQuery = "SELECT type FROM Users WHERE username = '$username'";
	
	// execute the query
	$uResult = mysql_query($uQuery);
	
	// figure out if rows were returned
	if(mysql_num_rows($uResult) > 0)
	{
		// username already exists
		return true;
	}
	
	// username does not already exist
	return false;
}

// figure out whether a given email already exists
function emailExists($email)
{
	// build the SELECT query
	$eQuery = "SELECT type FROM Users WHERE email = '$email'";
	
	// execute the query
	$eResult = mysql_query($eQuery);
	
	// figure out if rows were returned
	if(mysql_num_rows($eResult) > 0)
	{
		// email already exists
		return true;
	}
	
	// email does not already exist
	return false;
}

// register a user in the database
function processRegistration($username, $password, $email, $firstName, $lastName)
{
	// build the INSERT query
	$registerQuery = "INSERT INTO Users (username,password,type,email,firstName,lastName) VALUES (";
	$registerQuery .= "'$username',";
	$registerQuery .= getEncryptionFunction($password) . ","; // encrypt the password
	$registerQuery .= "'Faculty',"; // give the user a Faculty type by default
	$registerQuery .= "'$email',";
	$registerQuery .= "'$firstName',";
	$registerQuery .= "'$lastName'";
	$registerQuery .= ")";
	
	// execute the query
	mysql_query($registerQuery);
}

// add a course to the database
function addCourse($courseDept, $courseNumber, $courseName, $courseType, $courseStudents, $courseNotes)
{
	// build the INSERT query
	$addQuery = "INSERT INTO Classes (ClassName,ClassType,ClassDept,ClassNumber,ClassStudents,ClassNotes) VALUES (";
	$addQuery .= "'$courseName',";
	$addQuery .= "'$courseType',";
	$addQuery .= "'$courseDept',";
	$addQuery .= "'$courseNumber',";
	$addQuery .= "'$courseStudents',";
	$addQuery .= "'$courseNotes'";
	$addQuery .= ")";
	
	// execute the query
	mysql_query($addQuery);
	
	// get the last auto-generated ID
	$id = mysql_insert_id();
	
	// build an array of the course items
	$course = array();
	$course['ID'] = $id;
	$course['ClassDept'] = $courseDept;
	$course['ClassNumber'] = $courseNumber;
	$course['ClassCode'] = $courseDept . " " . $courseNumber;
	$course['ClassName'] = $courseName;
	$course['ClassType'] = $courseType;
	$course['ClassStudents'] = $courseStudents;
	$course['ClassNotes'] = $courseNotes;
	
	// generate the HTML to return
	generateCourseItemDisplay($course);
}

// generate the item listing for a course on the Manage Courses page
function generateCourseItemDisplay($class)
{
	echo "<div class='manageCoursesItem manageCoursesItem" . $class['ClassType'] . "' id='course" . $class['ID'] . "' onClick='$(\"#course" . $class['ID'] . "info\").slideToggle(400)'><span style='float:left;cursor:pointer' id='course" . $class['ID'] . "code' title='" . $class['ClassName'] . "'>" . $class['ClassCode'] . "</span><span class='ui-icon ui-icon-close' style='float:right;cursor:pointer;' onClick='deleteCourse(" . $class['ID'] . ",\"" . $class['ClassCode'] . "\");event.cancelBubble=true;'></span><span class='ui-icon ui-icon-pencil' style='float:right;cursor:pointer;' onClick='showEditCourseDialog(" . $class['ID'] . ");event.cancelBubble=true;'></span></div>";
	echo "<span id='course" . $class['ID'] . "info' style='width:400px;display:none;margin-bottom:5px;margin-left:auto;margin-right:auto'><b>Name:</b> " . stripslashes($class['ClassName']) . "<br /><b>Type:</b> " . getClassTypeName($class['ClassType']) . "<br /><b>Students:</b> " . $class['ClassStudents'] . "<br /><b>Notes:</b> " . stripslashes($class['ClassNotes']) . "</span>";
	echo "<input type='hidden' id='course" . $class['ID'] . "ItemType' value='" . $class['ClassType'] . "' />";
	echo "<input type='hidden' id='course" . $class['ID'] . "ItemDept' value='" . $class['ClassDept'] . "' />";
	echo "<input type='hidden' id='course" . $class['ID'] . "ItemNum' value='" . $class['ClassNumber'] . "' />";
	echo "<input type='hidden' id='course" . $class['ID'] . "ItemStudents' value='" . $class['ClassStudents'] . "' />";
	echo "<input type='hidden' id='course" . $class['ID'] . "ItemNotes' value='" . stripslashes($class['ClassNotes']) . "' />";
	echo "<input type='hidden' id='course" . $class['ID'] . "ItemName' value='" . stripslashes($class['ClassName']) . "' />";
}

// edit a course in the database
function editCourse($courseID, $courseDept, $courseNumber, $courseName, $courseType, $courseStudents, $courseNotes)
{
	// build the UPDATE query
	$updateQuery = "UPDATE Classes SET ClassDept = '$courseDept', ClassNumber = '$courseNumber', ClassName = '$courseName', ClassType = '$courseType', ClassStudents = $courseStudents, ClassNotes = '$courseNotes' WHERE ID = $courseID";
	
	// execute the query
	mysql_query($updateQuery);
}

// remove a course from the database
function removeCourse($courseID)
{
	// build the DELETE query
	$deleteQuery = "DELETE FROM Classes WHERE ID = '$courseID'";
	
	// execute the query
	mysql_query($deleteQuery);
}

// assign an instructor to a course in the database
function assignCourse($instructorID, $courseID, $term)
{
	// build the INSERT query
	$insertQuery = "INSERT INTO ClassesForInstructors(classID,instructorID,Term) VALUES($courseID,$instructorID,'$term')";
	
	// execute the query
	mysql_query($insertQuery);
}

// remove a course assignment from the database
function removeCourseAssignment($instructorID, $courseID, $term)
{
	// build the DELETE query
	$deleteQuery = "DELETE FROM ClassesForInstructors WHERE classID = $courseID AND instructorID = $instructorID AND Term = '$term'";
	
	// execute the query
	mysql_query($deleteQuery);
}

// get all the classes ordered by class department and class number in ascending order
function getClassList()
{
	$classes = array();
	
	// build the SELECT query
	$classesQuery = "SELECT ID,ClassName,ClassType,ClassDept,ClassNumber,ClassStudents,ClassNotes FROM Classes WHERE ID IS NOT NULL ORDER BY ClassDept,ClassNumber ASC";
	
	// execute the query
	$classesResult = mysql_query($classesQuery);
	
	// get each row back as an associative array
	$i = 0;
	while($row = mysql_fetch_assoc($classesResult))
	{
		$classes[$i] = $row;
		
		// build the class code ourselves since MySQL ORDER BY is a stupid jerk and doesn't order properly with an actual ClassCode DB column
		$classes[$i]['ClassCode'] = $row['ClassDept'] . " " . $row['ClassNumber'];
		
		// get the instructors for this particular class
		$classes[$i]['Instructors'] = getInstructorsByCourse($row['ID']);
		
		$i++;
	}
	
	// return the list of classes
	return $classes;
}

// get the actual name of a class type given a class type code
function getClassTypeName($classType)
{
	$classTypeCode = sanitizeInput($classType);
	
	// build the SELECT query
	$classTypeSQL = "SELECT ClassTypeName FROM ClassTypes WHERE ClassType = '$classTypeCode'";
	
	// execute the query
	$classTypeResult = mysql_query($classTypeSQL);
	
	// get the first row back
	$classTypeRow = mysql_fetch_assoc($classTypeResult);
	
	// return the class type name
	if(!empty($classTypeRow['ClassTypeName']))
	{
		return $classTypeRow['ClassTypeName'];
	}

	return "No Idea";
}

// get a list of all the departments
function getDepartments()
{
	// build the SELECT query
	$departmentsSQL = "SELECT DepartmentID,DepartmentName FROM Departments WHERE DepartmentID IS NOT NULL ORDER BY DepartmentID ASC";
	
	// execute the query
	$departmentsResult = mysql_query($departmentsSQL);
	
	$departments = array();
	
	// get all the departments and add them to the array
	$i = 0;
	while($row = mysql_fetch_assoc($departmentsResult))
	{
		$departments[$i] = $row;
		$i++;
	}
	
	return $departments;
}

// get a list of all the class types
function getClassTypes()
{
	// build the SELECT query
	$classTypesSQL = "SELECT ClassType,ClassTypeName FROM ClassTypes WHERE ClassType IS NOT NULL";
	
	// execute the query
	$classTypesResult = mysql_query($classTypesSQL);
	
	$classTypes = array();
	
	// get all the class types and add them to the array
	$i = 0;
	while($row = mysql_fetch_assoc($classTypesResult))
	{
		$classTypes[$i] = $row;
		$i++;
	}
	
	return $classTypes;
}

// get all instructors indices assigned to a particular course
function getInstructorsByCourse($courseID)
{
	// build the SELECT query
	$selectSQL = "SELECT ClassesForInstructors.instructorID,ClassesForInstructors.Term,Instructors.FirstName,Instructors.LastName FROM ClassesForInstructors JOIN Instructors ON ClassesForInstructors.instructorID = Instructors.instructorID WHERE classID = $courseID";
	
	// execute the query
	$selectResult = mysql_query($selectSQL);
	
	// get all the instructor IDs
	$instructors = array();
	$i = 0;
	while($row = mysql_fetch_assoc($selectResult))
	{
		$instructors[$i] = $row;
		$i++;
	}
	
	return $instructors;
}

?>